﻿using System.Data;
using System.Data.SqlClient;
using Pantry.Entity;
using Pantry.Commons;

namespace Pantry.DataAccess
{
    public class PremiseDao
    {
        private readonly SqlConnection _sqlConnection;
        public PremiseDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        #region Method

        /// <summary>
        /// Adds the premise.
        /// </summary>
        /// <param name="premises">The premises.</param>
        /// <returns></returns>
        public int AddPremise(Premises premises)
        {
            var sqlParam = new SqlParameter[63];
                sqlParam[0] = new SqlParameter("@LocationName", SqlDbType.NVarChar) { Value = premises.LocationName };
                sqlParam[1] = new SqlParameter("@KnownAs", SqlDbType.NVarChar) { Value = premises.KnownAs };
                sqlParam[2] = new SqlParameter("@PrimaryLocation", SqlDbType.Bit) { Value = premises.PrimaryLocation };
                sqlParam[3] = new SqlParameter("@LocationManaged", SqlDbType.Bit) { Value = premises.LocationManaged };
                sqlParam[4] = new SqlParameter("@OrgnisationId", SqlDbType.Int) { Value = premises.OrgnisationId };
                sqlParam[5] = new SqlParameter("@LocationStatus", SqlDbType.NVarChar) { Value = premises.LocationStatus };
                sqlParam[6] = new SqlParameter("@StNetworkConectivity", SqlDbType.Bit) { Value = premises.StNetworkConectivity };
                sqlParam[7] = new SqlParameter("@LocationStatusDate", SqlDbType.NVarChar) { Value = premises.LocationStatusDate };
                sqlParam[8] = new SqlParameter("@LocationType", SqlDbType.NVarChar) { Value = premises.LocationType };
                sqlParam[9] = new SqlParameter("@LocationDescription", SqlDbType.NVarChar) { Value = premises.LocationDescription };
                sqlParam[10] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = premises.AddressLine1 };
                sqlParam[11] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = premises.AddressLine2 };
                sqlParam[12] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = premises.AddressLine3 };
                sqlParam[13] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = premises.PhoneNumber };
                sqlParam[14] = new SqlParameter("@Postcode", SqlDbType.NVarChar) { Value = premises.Postcode };
                sqlParam[15] = new SqlParameter("@FaxNumber", SqlDbType.NVarChar) { Value = premises.FaxNumber };
                sqlParam[16] = new SqlParameter("@CityTown", SqlDbType.NVarChar) { Value = premises.CityTown };
                sqlParam[17] = new SqlParameter("@MinicommNumber", SqlDbType.NVarChar) { Value = premises.MinicommNumber };
                sqlParam[18] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = premises.County };
                sqlParam[19] = new SqlParameter("@IsNewShop", SqlDbType.Bit) { Value = premises.IsNewShop };
                sqlParam[20] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = premises.CountryId };
                sqlParam[21] = new SqlParameter("@ShopFlagDate", SqlDbType.NVarChar) { Value = premises.ShopFlagDate };
                sqlParam[22] = new SqlParameter("@SpecialistShop", SqlDbType.NVarChar) { Value = premises.SpecialistShop };
                sqlParam[23] = new SqlParameter("@Accreditations", SqlDbType.NVarChar) { Value = premises.Accreditations };
                sqlParam[24] = new SqlParameter("@JcpOffices", SqlDbType.NVarChar) { Value = premises.JcpOffices };
                sqlParam[25] = new SqlParameter("@MediaContact", SqlDbType.NVarChar) { Value = premises.MediaContact };
                sqlParam[26] = new SqlParameter("@CateringContact", SqlDbType.NVarChar) { Value = premises.CateringContact };
                sqlParam[27] = new SqlParameter("@CateringType", SqlDbType.Int) { Value = premises.CateringType };
                sqlParam[28] = new SqlParameter("@LocalDemographicIssue", SqlDbType.NVarChar) { Value = premises.LocalDemographicIssue };
                sqlParam[29] = new SqlParameter("@IsNetwork", SqlDbType.Int) { Value = premises.IsNetwork };
                sqlParam[30] = new SqlParameter("@ClientItFacilityDetails", SqlDbType.NVarChar) { Value = premises.ClientItFacilityDetails };
                sqlParam[31] = new SqlParameter("@LocalDemographicNotes", SqlDbType.NVarChar) { Value = premises.LocalDemographicNotes };
                sqlParam[32] = new SqlParameter("@RoomAvailability", SqlDbType.Bit) { Value = premises.RoomAvailability };
                sqlParam[33] = new SqlParameter("@VolunteeringOpportunities", SqlDbType.Bit) { Value = premises.VolunteeringOpportunities };
                sqlParam[34] = new SqlParameter("@OutreachLocation", SqlDbType.NVarChar) { Value = premises.OutreachLocation };
                sqlParam[35] = new SqlParameter("@LocalHotel", SqlDbType.NVarChar) { Value = premises.LocalHotel };
                sqlParam[36] = new SqlParameter("@TravelDetails", SqlDbType.NVarChar) { Value = premises.TravelDetails };
                sqlParam[37] = new SqlParameter("@VisitorParkingOnsite", SqlDbType.Bit) { Value = premises.VisitorParkingOnsite };
                sqlParam[38] = new SqlParameter("@TravelNearestBus", SqlDbType.NVarChar) { Value = premises.TravelNearestBus };
                sqlParam[39] = new SqlParameter("@VisitorParkingSpaces", SqlDbType.Int) { Value = premises.VisitorParkingSpaces };
                sqlParam[40] = new SqlParameter("@TravelNearestRail", SqlDbType.NVarChar) { Value = premises.TravelNearestRail };
                sqlParam[41] = new SqlParameter("@TravelNearestAirport", SqlDbType.NVarChar) { Value = premises.TravelNearestAirport };
                sqlParam[42] = new SqlParameter("@VisitorParkingAlternative", SqlDbType.NVarChar) { Value = premises.VisitorParkingAlternative };
                sqlParam[43] = new SqlParameter("@HostVisits", SqlDbType.Bit) { Value = premises.HostVisits };
                sqlParam[44] = new SqlParameter("@HostingContact", SqlDbType.NVarChar) { Value = premises.HostingContact };
                sqlParam[45] = new SqlParameter("@RoomOnlyRate", SqlDbType.Float) { Value = premises.RoomOnlyRate };
                sqlParam[46] = new SqlParameter("@NegotiatedRoomOnlyRate", SqlDbType.Float) { Value = premises.LocationName };
                sqlParam[47] = new SqlParameter("@BbRate", SqlDbType.Float) { Value = premises.BbRate };
                sqlParam[48] = new SqlParameter("@BbNegotiatedRate", SqlDbType.Float) { Value = premises.BbNegotiatedRate };
                sqlParam[49] = new SqlParameter("@DdRate", SqlDbType.Float) { Value = premises.DdRate };
                sqlParam[50] = new SqlParameter("@DdNegotiatedRate", SqlDbType.Float) { Value = premises.DdNegotiatedRate };
                sqlParam[51] = new SqlParameter("@RateDay", SqlDbType.Float) { Value = premises.RateDay };
                sqlParam[52] = new SqlParameter("@NegotiatedRateDay", SqlDbType.Float) { Value = premises.NegotiatedRateDay };
                sqlParam[53] = new SqlParameter("@TeaAndCoffee", SqlDbType.NVarChar) { Value = premises.TeaAndCoffee };
                sqlParam[54] = new SqlParameter("@Lunch", SqlDbType.Bit) { Value = premises.Lunch };
                sqlParam[55] = new SqlParameter("@LastNegotiatedDate", SqlDbType.NVarChar) { Value = premises.LastNegotiatedDate };
                sqlParam[56] = new SqlParameter("@ReNegotiatedOn", SqlDbType.NVarChar) { Value = premises.ReNegotiatedOn };
                sqlParam[57] = new SqlParameter("@NoOfMeetingRoom", SqlDbType.Int) { Value = premises.NoOfMeetingRoom };
                sqlParam[58] = new SqlParameter("@PreferedStatus", SqlDbType.NVarChar) { Value = premises.PreferedStatus };
                sqlParam[59] = new SqlParameter("@MeetingRoomRatePerDay", SqlDbType.Float) { Value = premises.MeetingRoomRatePerDay };
                sqlParam[60] = new SqlParameter("@Codings", SqlDbType.NVarChar) { Value = premises.Codings };
                sqlParam[61] = new SqlParameter("@Comments", SqlDbType.NVarChar) { Value = premises.Comments };
                sqlParam[62] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = premises.IsActive };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.SqlAddPremise, sqlParam);
        }
        /// <summary>
        /// Updates the premise.
        /// </summary>
        /// <param name="premises">The premises.</param>
        /// <returns></returns>
        public int UpdatePremise(Premises premises)
        {
            var sqlParam = new SqlParameter[64];
            sqlParam[0] = new SqlParameter("@LocationName", SqlDbType.NVarChar) { Value = premises.LocationName };
            sqlParam[1] = new SqlParameter("@KnownAs", SqlDbType.NVarChar) { Value = premises.KnownAs };
            sqlParam[2] = new SqlParameter("@PrimaryLocation", SqlDbType.Bit) { Value = premises.PrimaryLocation };
            sqlParam[3] = new SqlParameter("@LocationManaged", SqlDbType.Bit) { Value = premises.LocationManaged };
            sqlParam[4] = new SqlParameter("@OrgnisationId", SqlDbType.Int) { Value = premises.OrgnisationId };
            sqlParam[5] = new SqlParameter("@LocationStatus", SqlDbType.NVarChar) { Value = premises.LocationStatus };
            sqlParam[6] = new SqlParameter("@StNetworkConectivity", SqlDbType.Bit) { Value = premises.StNetworkConectivity };
            sqlParam[7] = new SqlParameter("@LocationStatusDate", SqlDbType.NVarChar) { Value = premises.LocationStatusDate };
            sqlParam[8] = new SqlParameter("@LocationType", SqlDbType.NVarChar) { Value = premises.LocationType };
            sqlParam[9] = new SqlParameter("@LocationDescription", SqlDbType.NVarChar) { Value = premises.LocationDescription };
            sqlParam[10] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = premises.AddressLine1 };
            sqlParam[11] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = premises.AddressLine2 };
            sqlParam[12] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = premises.AddressLine3 };
            sqlParam[13] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = premises.PhoneNumber };
            sqlParam[14] = new SqlParameter("@Postcode", SqlDbType.NVarChar) { Value = premises.Postcode };
            sqlParam[15] = new SqlParameter("@FaxNumber", SqlDbType.NVarChar) { Value = premises.FaxNumber };
            sqlParam[16] = new SqlParameter("@CityTown", SqlDbType.NVarChar) { Value = premises.CityTown };
            sqlParam[17] = new SqlParameter("@MinicommNumber", SqlDbType.NVarChar) { Value = premises.MinicommNumber };
            sqlParam[18] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = premises.County };
            sqlParam[19] = new SqlParameter("@IsNewShop", SqlDbType.Bit) { Value = premises.IsNewShop };
            sqlParam[20] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = premises.CountryId };
            sqlParam[21] = new SqlParameter("@ShopFlagDate", SqlDbType.NVarChar) { Value = premises.ShopFlagDate };
            sqlParam[22] = new SqlParameter("@SpecialistShop", SqlDbType.NVarChar) { Value = premises.SpecialistShop };
            sqlParam[23] = new SqlParameter("@Accreditations", SqlDbType.NVarChar) { Value = premises.Accreditations };
            sqlParam[24] = new SqlParameter("@JcpOffices", SqlDbType.NVarChar) { Value = premises.JcpOffices };
            sqlParam[25] = new SqlParameter("@MediaContact", SqlDbType.NVarChar) { Value = premises.MediaContact };
            sqlParam[26] = new SqlParameter("@CateringContact", SqlDbType.NVarChar) { Value = premises.CateringContact };
            sqlParam[27] = new SqlParameter("@CateringType", SqlDbType.Int) { Value = premises.CateringType };
            sqlParam[28] = new SqlParameter("@LocalDemographicIssue", SqlDbType.NVarChar) { Value = premises.LocalDemographicIssue };
            sqlParam[29] = new SqlParameter("@IsNetwork", SqlDbType.Int) { Value = premises.IsNetwork };
            sqlParam[30] = new SqlParameter("@ClientItFacilityDetails", SqlDbType.NVarChar) { Value = premises.ClientItFacilityDetails };
            sqlParam[31] = new SqlParameter("@LocalDemographicNotes", SqlDbType.NVarChar) { Value = premises.LocalDemographicNotes };
            sqlParam[32] = new SqlParameter("@RoomAvailability", SqlDbType.Bit) { Value = premises.RoomAvailability };
            sqlParam[33] = new SqlParameter("@VolunteeringOpportunities", SqlDbType.Bit) { Value = premises.VolunteeringOpportunities };
            sqlParam[34] = new SqlParameter("@OutreachLocation", SqlDbType.NVarChar) { Value = premises.OutreachLocation };
            sqlParam[35] = new SqlParameter("@LocalHotel", SqlDbType.NVarChar) { Value = premises.LocalHotel };
            sqlParam[36] = new SqlParameter("@TravelDetails", SqlDbType.NVarChar) { Value = premises.TravelDetails };
            sqlParam[37] = new SqlParameter("@VisitorParkingOnsite", SqlDbType.Bit) { Value = premises.VisitorParkingOnsite };
            sqlParam[38] = new SqlParameter("@TravelNearestBus", SqlDbType.NVarChar) { Value = premises.TravelNearestBus };
            sqlParam[39] = new SqlParameter("@VisitorParkingSpaces", SqlDbType.Int) { Value = premises.VisitorParkingSpaces };
            sqlParam[40] = new SqlParameter("@TravelNearestRail", SqlDbType.NVarChar) { Value = premises.TravelNearestRail };
            sqlParam[41] = new SqlParameter("@TravelNearestAirport", SqlDbType.NVarChar) { Value = premises.TravelNearestAirport };
            sqlParam[42] = new SqlParameter("@VisitorParkingAlternative", SqlDbType.NVarChar) { Value = premises.VisitorParkingAlternative };
            sqlParam[43] = new SqlParameter("@HostVisits", SqlDbType.Bit) { Value = premises.HostVisits };
            sqlParam[44] = new SqlParameter("@HostingContact", SqlDbType.NVarChar) { Value = premises.HostingContact };
            sqlParam[45] = new SqlParameter("@RoomOnlyRate", SqlDbType.Float) { Value = premises.RoomOnlyRate };
            sqlParam[46] = new SqlParameter("@NegotiatedRoomOnlyRate", SqlDbType.Float) { Value = premises.LocationName };
            sqlParam[47] = new SqlParameter("@BbRate", SqlDbType.Float) { Value = premises.BbRate };
            sqlParam[48] = new SqlParameter("@BbNegotiatedRate", SqlDbType.Float) { Value = premises.BbNegotiatedRate };
            sqlParam[49] = new SqlParameter("@DdRate", SqlDbType.Float) { Value = premises.DdRate };
            sqlParam[50] = new SqlParameter("@DdNegotiatedRate", SqlDbType.Float) { Value = premises.DdNegotiatedRate };
            sqlParam[51] = new SqlParameter("@RateDay", SqlDbType.Float) { Value = premises.RateDay };
            sqlParam[52] = new SqlParameter("@NegotiatedRateDay", SqlDbType.Float) { Value = premises.NegotiatedRateDay };
            sqlParam[53] = new SqlParameter("@TeaAndCoffee", SqlDbType.NVarChar) { Value = premises.TeaAndCoffee };
            sqlParam[54] = new SqlParameter("@Lunch", SqlDbType.Bit) { Value = premises.Lunch };
            sqlParam[55] = new SqlParameter("@LastNegotiatedDate", SqlDbType.NVarChar) { Value = premises.LastNegotiatedDate };
            sqlParam[56] = new SqlParameter("@ReNegotiatedOn", SqlDbType.NVarChar) { Value = premises.ReNegotiatedOn };
            sqlParam[57] = new SqlParameter("@NoOfMeetingRoom", SqlDbType.Int) { Value = premises.NoOfMeetingRoom };
            sqlParam[58] = new SqlParameter("@PreferedStatus", SqlDbType.NVarChar) { Value = premises.PreferedStatus };
            sqlParam[59] = new SqlParameter("@MeetingRoomRatePerDay", SqlDbType.Float) { Value = premises.MeetingRoomRatePerDay };
            sqlParam[60] = new SqlParameter("@Codings", SqlDbType.NVarChar) { Value = premises.Codings };
            sqlParam[61] = new SqlParameter("@Comments", SqlDbType.NVarChar) { Value = premises.Comments };
            sqlParam[62] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = premises.IsActive };
            sqlParam[63] = new SqlParameter("@PremiseId", SqlDbType.Int) { Value = premises.PremiseId };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.SqlUpdatePremise, sqlParam);
        }
        /// <summary>
        /// Gets all premise.
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllPremise()
        {
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlGetAllPremise).Tables[0];
        }
        /// <summary>
        /// Gets the premise by id.
        /// </summary>
        /// <param name="premiseId">The premise id.</param>
        /// <returns></returns>
        public DataTable GetPremiseById(int premiseId)
        {
            var sqlParam = new SqlParameter("@PremiseId", SqlDbType.Int) {Value = premiseId};
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlGetPremiseById, sqlParam).Tables[0];
        }
        /// <summary>
        /// Gets the type of the premise by location.
        /// </summary>
        /// <param name="locationType">Type of the location.</param>
        /// <returns></returns>
        public DataTable GetPremiseByLocationType(string locationType)
        {
            var sqlParam = new SqlParameter("@LocationType", SqlDbType.NVarChar) { Value = locationType };
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlGetPremiseByLocationType, sqlParam).Tables[0];
        }
        /// <summary>
        /// Checks the name exists.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public int CheckNameExists(string name)
        {
            var sqlParam = new SqlParameter("@LocationName", SqlDbType.NVarChar) {Value = name};
            return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, DBSchemas.SqlCheckNameExists, sqlParam);
        }
        #endregion

    }
}
